﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPI.OrganizationDomain.Model.Entity;
using KPI.Utility;
using System.Data.SqlClient;
using KPI.OrganizationDomain.Infrastructure.DbObject;
using System.Data;

namespace KPI.OrganizationDomain.Infrastructure.DbManager
{
    public class EmployeeManager
    {
        public EmployeeTab CreateEmployee(EmployeeTab employee, SqlConnection kpiConnection, SqlTransaction transaction)
        {
            SqlCommand command = kpiConnection.CreateCommand();
            if (transaction != null) command.Transaction = transaction;

            command.CommandText = @"SET IDENTITY_INSERT  [Employee]   ON;insert into Employee([FirstName], [LastName], Birthday, FirstWorkingDate, EmployeeID, [LoginName], [Email],MobilePhoneNumber,TelephoneNumber,CreatedDate, LastModifiedDate, Version, IsDeleted,EmployeeLevelID,EmployeeTitleID) 
values(@firstname, @lastname, @birthday, @firstworkingdate, @employeeid, @loginname, @email,@mobilephonenumber,@telephonenumber,@createdDate, @lastModifiedDate, 0, 0,@employeeLevelID,@employeeTitleID); 
select @newID=SCOPE_IDENTITY() ; SET IDENTITY_INSERT  [Employee]   OFF  ";
                       
            employee.Version = 0;
            employee.IsDeleted = false;

            command.Parameters.Add(new SqlParameter("@firstname", employee.FirstName));
            command.Parameters.Add(new SqlParameter("@lastname", employee.LastName));
            command.Parameters.Add(new SqlParameter("@birthday", employee.Birthday));
            command.Parameters.Add(new SqlParameter("@firstworkingdate", employee.FirstWorkingDate));
            command.Parameters.Add(new SqlParameter("@employeeid",employee.EmployeeID));

            command.Parameters.Add(new SqlParameter("@loginname", employee.LoginName));
            command.Parameters.Add(new SqlParameter("@email", employee.Email));
            command.Parameters.Add(new SqlParameter("@mobilephonenumber", employee.MobilePhoneNumber));
            command.Parameters.Add(new SqlParameter("@TelephoneNumber", employee.TelephoneNumber));
            command.Parameters.Add(new SqlParameter("@createdDate", DateTime.Now));
            command.Parameters.Add(new SqlParameter("@lastModifiedDate", DateTime.Now));
            command.Parameters.Add(new SqlParameter("@employeeLevelID", employee.EmployeeLevelID));
            command.Parameters.Add(new SqlParameter("@employeeTitleID", employee.EmployeeTitleID));

            SqlParameter newID = new SqlParameter("@newId", SqlDbType.Int);
            newID.Direction = ParameterDirection.Output;
            command.Parameters.Add(newID);

            command.ExecuteNonQuery();

            employee.EmployeeID = Convert.ToInt32(newID.Value);
            return employee;

           }


        public DataTable SelectEmployeeLevel(SqlConnection kpiConnection, SqlTransaction transaction)
        {
            SqlCommand command = kpiConnection.CreateCommand();
            if (transaction != null) command.Transaction = transaction;

            command.CommandText = @"SET IDENTITY_INSERT  [EmployeeLevel]   ON; Select * from EmployeeLevel ; SET IDENTITY_INSERT  [EmployeeLevel]   OFF  ";

            SqlDataAdapter adatper = new SqlDataAdapter(command.CommandText, kpiConnection);
            DataTable dtEmployeeLevel = new DataTable();
            adatper.Fill(dtEmployeeLevel);
         
            return dtEmployeeLevel;
        }

        public DataTable SelectEmployeeTitle(SqlConnection kpiConnection, SqlTransaction transaction)
        {
            SqlCommand command = kpiConnection.CreateCommand();
            if (transaction != null) command.Transaction = transaction;

            command.CommandText = @"SET IDENTITY_INSERT  [EmployeeTitle]   ON; Select * from EmployeeTitle ; SET IDENTITY_INSERT  [EmployeeTitle]   OFF  ";

            SqlDataAdapter adatper = new SqlDataAdapter(command.CommandText, kpiConnection);
            DataTable dtEmployeeTitle= new DataTable();
            adatper.Fill(dtEmployeeTitle);

            return dtEmployeeTitle;
        }

        public DataTable SelectEmployeeDirector(SqlConnection kpiConnection, SqlTransaction transaction)
        {
            SqlCommand command = kpiConnection.CreateCommand();
            if (transaction != null) command.Transaction = transaction;

            command.CommandText = @"SET IDENTITY_INSERT  [EmployeeTitle]   ON; select * from dbo.Employee a inner join dbo.EmployeeLevel b on b.EmployeeLevelID>=3 and a.EmployeeLevelID = b.EmployeeLevelID left join dbo.EmployeeTitle c on c.EmployeeTitleID =a.EmployeeTitleID where c.Name like '%Manager%'; SET IDENTITY_INSERT  [EmployeeTitle]   OFF  ";

            SqlDataAdapter adatper = new SqlDataAdapter(command.CommandText, kpiConnection);
            DataTable dtEmployeeDirector = new DataTable();
            adatper.Fill(dtEmployeeDirector);

            return dtEmployeeDirector;
        }

    }
}
